SET NOCOUNT ON
GO

IF OBJECT_ID('Serialize_') IS NOT NULL DROP PROCEDURE Serialize_
IF OBJECT_ID('Serialize_JSON') IS NOT NULL DROP PROCEDURE Serialize_JSON
IF OBJECT_ID('Serialize_CSV') IS NOT NULL DROP PROCEDURE Serialize_CSV
IF OBJECT_ID('Serialize_XML') IS NOT NULL DROP PROCEDURE Serialize_XML
GO

CREATE PROCEDURE [dbo].[Serialize_] @DelegateName varchar(50), @ParameterSQL AS VARCHAR(MAX)
AS
BEGIN
	RETURN NULL
END
GO

-- THIS SAMPLE IS BORROWED FROM JAMIN QUIMBY
-- THE PROCEDURENAME IS SLIGHTLY ADJUSTED TO FIT THE CONVENTION
-- http://jaminquimby.com/joomla253/servers/95-sql/sql-2008/145-code-tsql-convert-query-to-json
GO
CREATE PROCEDURE[dbo].[Serialize_JSON](
	@ParameterSQL AS VARCHAR(MAX)
)
AS
BEGIN
 
	DECLARE @SQL NVARCHAR(MAX)
	DECLARE @XMLString VARCHAR(MAX)
	DECLARE @XML XML
	DECLARE @Paramlist NVARCHAR(1000)
	SET @Paramlist = N'@XML XML OUTPUT'
	SET @SQL = 'WITH PrepareTable (XMLString)'
	SET @SQL = @SQL + 'AS('
	SET @SQL = @SQL + @ParameterSQL+ 'FOR XML RAW,TYPE,ELEMENTS'
	SET @SQL = @SQL + ')'
	SET @SQL = @SQL + 'SELECT @XML=[XMLString]FROM[PrepareTable]'
	EXEC sp_executesql @SQL, @Paramlist, @XML=@XML OUTPUT
	SET @XMLString=CAST(@XML AS VARCHAR(MAX))
 
	DECLARE @JSON VARCHAR(MAX)
	DECLARE @Row VARCHAR(MAX)
	DECLARE @RowStart INT
	DECLARE @RowEnd INT
	DECLARE @FieldStart INT
	DECLARE @FieldEnd INT
	DECLARE @KEY VARCHAR(MAX)
	DECLARE @Value VARCHAR(MAX)
 
	DECLARE @StartRoot VARCHAR(100);SET @StartRoot='<row>'
	DECLARE @EndRoot VARCHAR(100);SET @EndRoot='</row>'
	DECLARE @StartField VARCHAR(100);SET @StartField='<'
	DECLARE @EndField VARCHAR(100);SET @EndField='>'
 
	SET @RowStart=CharIndex(@StartRoot,@XMLString,0)
	SET @JSON=''
	WHILE @RowStart>0
	BEGIN
		SET @RowStart=@RowStart+Len(@StartRoot)
		SET @RowEnd=CharIndex(@EndRoot,@XMLString,@RowStart)
		SET @Row=SubString(@XMLString,@RowStart,@RowEnd-@RowStart)
		SET @JSON=@JSON+'{'
 
		-- for each row
		SET @FieldStart=CharIndex(@StartField,@Row,0)
		WHILE @FieldStart>0
		BEGIN
			-- parse node key
			SET @FieldStart=@FieldStart+Len(@StartField)
			SET @FieldEnd=CharIndex(@EndField,@Row,@FieldStart)
			SET @KEY=SubString(@Row,@FieldStart,@FieldEnd-@FieldStart)
			SET @JSON=@JSON+'"'+@KEY+'":'
			-- parse node value
			SET @FieldStart=@FieldEnd+1
			SET @FieldEnd=CharIndex('</',@Row,@FieldStart)
			SET @Value=SubString(@Row,@FieldStart,@FieldEnd-@FieldStart)
			SET @JSON=@JSON+'"'+@Value+'",'
 
			SET @FieldStart=@FieldStart+Len(@StartField)
			SET @FieldEnd=CharIndex(@EndField,@Row,@FieldStart)
			SET @FieldStart=CharIndex(@StartField,@Row,@FieldEnd)
		END    
		IF LEN(@JSON)>0SET @JSON=SubString(@JSON,0,LEN(@JSON))
		SET @JSON=@JSON+'},'
		--/ for each row
 
		SET @RowStart=CharIndex(@StartRoot,@XMLString,@RowEnd)
	END
	IF LEN(@JSON)>0SET @JSON=SubString(@JSON,0,LEN(@JSON))
	SET @JSON='['+@JSON+']'
	SELECT @JSON
 
END
GO

CREATE PROCEDURE[dbo].[Serialize_XML](
	@ParameterSQL AS VARCHAR(MAX)
)
AS
BEGIN
 
	DECLARE @SQL NVARCHAR(MAX)
	SET @SQL = @ParameterSQL+ 'FOR XML AUTO,TYPE,ELEMENTS'
 	EXEC (@SQL)
 
END
GO

CREATE PROCEDURE[dbo].[Serialize_CSV](
	@ParameterSQL AS VARCHAR(MAX)
)
AS
BEGIN
 
	DECLARE @SQL NVARCHAR(MAX)
	DECLARE @XMLString VARCHAR(MAX)
	DECLARE @XML XML
	DECLARE @Paramlist NVARCHAR(1000)
	SET @Paramlist = N'@XML XML OUTPUT'
	SET @SQL = 'WITH PrepareTable (XMLString)'
	SET @SQL = @SQL + 'AS('
	SET @SQL = @SQL + @ParameterSQL+ 'FOR XML RAW,TYPE,ELEMENTS'
	SET @SQL = @SQL + ')'
	SET @SQL = @SQL + 'SELECT @XML=[XMLString]FROM[PrepareTable]'
	EXEC sp_executesql @SQL, @Paramlist, @XML=@XML OUTPUT
	SET @XMLString=CAST(@XML AS VARCHAR(MAX))
 
	DECLARE @CSV VARCHAR(MAX)
	DECLARE @CSVHead VARCHAR(max)
	DECLARE @CSVRow VARCHAR(max)
	DECLARE @Row VARCHAR(MAX)
	DECLARE @RowStart INT
	DECLARE @RowEnd INT
	DECLARE @FieldStart INT
	DECLARE @FieldEnd INT
	DECLARE @KEY VARCHAR(MAX)
	DECLARE @Value VARCHAR(MAX)
	DECLARE @Head bit
 
	DECLARE @StartRoot VARCHAR(100);SET @StartRoot='<row>'
	DECLARE @EndRoot VARCHAR(100);SET @EndRoot='</row>'
	DECLARE @StartField VARCHAR(100);SET @StartField='<'
	DECLARE @EndField VARCHAR(100);SET @EndField='>'
	
 
	SET @RowStart=CharIndex(@StartRoot,@XMLString,0)
	SET @CSV=''
	SET @Head = 1
	WHILE @RowStart>0
	BEGIN
		SET @RowStart=@RowStart+Len(@StartRoot)
		SET @RowEnd=CharIndex(@EndRoot,@XMLString,@RowStart)
		SET @Row=SubString(@XMLString,@RowStart,@RowEnd-@RowStart)
		SET @CSVHead = ''
		SET @CSVRow = ''

		-- for each row
		SET @FieldStart=CharIndex(@StartField,@Row,0)
		WHILE @FieldStart>0
		BEGIN
			-- parse node key
			SET @FieldStart=@FieldStart+Len(@StartField)
			SET @FieldEnd=CharIndex(@EndField,@Row,@FieldStart)
			SET @KEY=SubString(@Row,@FieldStart,@FieldEnd-@FieldStart)
			IF @Head = 1
			BEGIN
				SET @CSVHead=@CSVHead+'"'+@KEY+'";'	
			END
			-- parse node value
			SET @FieldStart=@FieldEnd+1
			SET @FieldEnd=CharIndex('</',@Row,@FieldStart)
			SET @Value=SubString(@Row,@FieldStart,@FieldEnd-@FieldStart)
			SET @CSVRow=@CSVRow+'"'+@Value+'";'
 
			SET @FieldStart=@FieldStart+Len(@StartField)
			SET @FieldEnd=CharIndex(@EndField,@Row,@FieldStart)
			SET @FieldStart=CharIndex(@StartField,@Row,@FieldEnd)
		END

		IF @Head = 1
		BEGIN
			IF LEN(@CSVHead)>0 SET @CSVHead=SubString(@CSVHead,0,LEN(@CSVHead))	
			SET @CSV+=@CSVHead + CHAR(13) + CHAR(10)
			SET @Head = 0
		END
		
		IF LEN(@CSVRow)>0 SET @CSVRow=SubString(@CSVRow,0,LEN(@CSVRow))
		SET @CSV+=@CSVRow + CHAR(13) + CHAR(10)
		--/ for each row
 
		SET @RowStart=CharIndex(@StartRoot,@XMLString,@RowEnd)
	END

	SELECT @CSV 
END
GO

exec QIC.Compile

exec [dbo].[Serialize_] 'JSON', 'select * from [sys].[objects]'
exec [dbo].[Serialize_] 'XML', 'select * from [sys].[objects]'
exec [dbo].[Serialize_] 'CSV', 'select * from [sys].[objects]'

GO

IF OBJECT_ID('Serialize_') IS NOT NULL DROP PROCEDURE Serialize_
IF OBJECT_ID('Serialize_JSON') IS NOT NULL DROP PROCEDURE Serialize_JSON
IF OBJECT_ID('Serialize_CSV') IS NOT NULL DROP PROCEDURE Serialize_CSV
IF OBJECT_ID('Serialize_XML') IS NOT NULL DROP PROCEDURE Serialize_XML
GO
